-- 银行信息初始化数据修复脚本
-- 解决BIN码重复导致的UNIQUE KEY约束违反问题

-- 首先清理可能已经插入的部分数据
DELETE FROM [dbo].[BankBinMapping];
DELETE FROM [dbo].[BankNameAlias];
DELETE FROM [dbo].[BankInfo];

-- 重置自增ID
DBCC CHECKIDENT ('[dbo].[BankInfo]', RESEED, 0);
DBCC CHECKIDENT ('[dbo].[BankBinMapping]', RESEED, 0);
DBCC CHECKIDENT ('[dbo].[BankNameAlias]', RESEED, 0);

-- 插入银行基础信息
INSERT INTO [dbo].[BankInfo] ([StandardName], [ShortName], [EnglishName], [BankCode], [CardLengthRule], [IsActive], [IsCustom], [CreatedBy], [Remark])
VALUES 
('中国工商银行', '工行', 'ICBC', '102', '16-19位', 1, 0, 'SYSTEM', '中国工商银行股份有限公司'),
('中国农业银行', '农行', 'ABC', '103', '16-19位', 1, 0, 'SYSTEM', '中国农业银行股份有限公司'),
('中国银行', '中行', 'BOC', '104', '16-19位', 1, 0, 'SYSTEM', '中国银行股份有限公司'),
('中国建设银行', '建行', 'CCB', '105', '16-19位', 1, 0, 'SYSTEM', '中国建设银行股份有限公司'),
('交通银行', '交行', 'BOCOM', '301', '16-19位', 1, 0, 'SYSTEM', '交通银行股份有限公司'),
('招商银行', '招行', 'CMB', '308', '16-19位', 1, 0, 'SYSTEM', '招商银行股份有限公司'),
('上海浦东发展银行', '浦发银行', 'SPDB', '310', '16-19位', 1, 0, 'SYSTEM', '上海浦东发展银行股份有限公司'),
('中国民生银行', '民生银行', 'CMBC', '305', '16-19位', 1, 0, 'SYSTEM', '中国民生银行股份有限公司'),
('兴业银行', '兴业银行', 'CIB', '309', '16-19位', 1, 0, 'SYSTEM', '兴业银行股份有限公司'),
('中国光大银行', '光大银行', 'CEB', '303', '16-19位', 1, 0, 'SYSTEM', '中国光大银行股份有限公司'),
('中信银行', '中信银行', 'CITIC', '302', '16-19位', 1, 0, 'SYSTEM', '中信银行股份有限公司'),
('华夏银行', '华夏银行', 'HXB', '304', '16-19位', 1, 0, 'SYSTEM', '华夏银行股份有限公司'),
('广东发展银行', '广发银行', 'GDB', '306', '16-19位', 1, 0, 'SYSTEM', '广发银行股份有限公司'),
('平安银行', '平安银行', 'PAB', '307', '16-19位', 1, 0, 'SYSTEM', '平安银行股份有限公司'),
('深圳发展银行', '深发银行', 'SDB', '307001', '16-19位', 1, 0, 'SYSTEM', '深圳发展银行股份有限公司');

-- 插入BIN码映射数据（修复重复问题）
-- 中国工商银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('102801'), ('102802'), ('102803'), ('622200'), ('622202'), ('622203'), 
    ('622208'), ('955880'), ('621225'), ('621226'), ('621227'), ('621281'), 
    ('621288'), ('621721'), ('621722'), ('621723')
) bin(BinCode)
WHERE b.StandardName = '中国工商银行';

-- 中国农业银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('103000'), ('622848'), ('622849'), ('622850'), ('622851'), ('622852'), 
    ('622853'), ('622854'), ('622855'), ('622856'), ('622857'), ('622858'), 
    ('622859'), ('622860'), ('621671'), ('621672')
) bin(BinCode)
WHERE b.StandardName = '中国农业银行';

-- 中国银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('104000'), ('621660'), ('621661'), ('621662'), ('621663'), ('621665'), 
    ('621667'), ('621668'), ('621669'), ('621666'), ('456351'), ('601382'), 
    ('622752'), ('622753'), ('622755'), ('622756')
) bin(BinCode)
WHERE b.StandardName = '中国银行';

-- 中国建设银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('105000'), ('621700'), ('621701'), ('621702'), ('621703'), ('621704'), 
    ('621705'), ('621706'), ('621707'), ('621708'), ('621709'), ('621710'), 
    ('621711'), ('356869'), ('356868'), ('622280')
) bin(BinCode)
WHERE b.StandardName = '中国建设银行';

-- 交通银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('301000'), ('622260'), ('622261'), ('622262'), ('622263'), ('622264'), 
    ('622265'), ('622266'), ('622267'), ('622268'), ('622269'), ('622258'), 
    ('622259'), ('521899')
) bin(BinCode)
WHERE b.StandardName = '交通银行';

-- 招商银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('308000'), ('621483'), ('621485'), ('621486'), ('356885'), ('356886'), 
    ('356887'), ('356888'), ('356889'), ('356890'), ('622575'), ('622576'), 
    ('622577'), ('622578'), ('622579'), ('622580'), ('622581'), ('622582'), 
    ('622588'), ('622598')
) bin(BinCode)
WHERE b.StandardName = '招商银行';

-- 上海浦东发展银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('310000'), ('621998'), ('621999'), ('622516'), ('622517'), ('622518'), 
    ('404738'), ('404739'), ('498451'), ('515672'), ('517650'), ('525998'), 
    ('622521')
) bin(BinCode)
WHERE b.StandardName = '上海浦东发展银行';

-- 中国民生银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('305000'), ('622600'), ('622601'), ('622602'), ('622603'), ('415599'), 
    ('421393'), ('421865'), ('421869'), ('421870'), ('622615'), ('622617'), 
    ('622619'), ('622622')
) bin(BinCode)
WHERE b.StandardName = '中国民生银行';

-- 兴业银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('309391'), ('622902'), ('622901'), ('486493'), ('486494'), ('523952'), 
    ('356859'), ('486861'), ('523036'), ('438125'), ('438126'), ('451289'), 
    ('527414')
) bin(BinCode)
WHERE b.StandardName = '兴业银行';

-- 中国光大银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('303000'), ('622650'), ('622655'), ('622658'), ('622660'), ('356837'), 
    ('356838'), ('356839'), ('356840'), ('406252'), ('406254'), ('481699')
) bin(BinCode)
WHERE b.StandardName = '中国光大银行';

-- 中信银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('302000'), ('622678'), ('622679'), ('622680'), ('622688'), ('622689'), 
    ('622690'), ('622691'), ('622692'), ('622696'), ('622698'), ('400360'), 
    ('403391'), ('404157'), ('404171'), ('404172'), ('404173'), ('404174'), 
    ('421437')
) bin(BinCode)
WHERE b.StandardName = '中信银行';

-- 华夏银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('304000'), ('622630'), ('622631'), ('622632'), ('622633'), ('539867'), 
    ('539868'), ('528709'), ('622637'), ('622638'), ('622639')
) bin(BinCode)
WHERE b.StandardName = '华夏银行';

-- 广东发展银行（移除重复的BIN码）
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('306000'), ('406365'), ('520152'), ('520382'), 
    ('541709'), ('541710'), ('548844'), ('552794'), ('622568')
    -- 移除了重复的 ('622525'), ('622526')
) bin(BinCode)
WHERE b.StandardName = '广东发展银行';

-- 平安银行
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('307000'), ('622155'), ('622156'), ('622157'), ('622158'), ('622159'), 
    ('627066'), ('627067'), ('627068'), ('998800'), ('998801'), ('998802')
) bin(BinCode)
WHERE b.StandardName = '平安银行';

-- 深圳发展银行（保留原有的BIN码，包括与广发银行重复的）
INSERT INTO [dbo].[BankBinMapping] ([BankId], [BinCode], [CardType], [CreatedBy])
SELECT b.Id, bin.BinCode, '借记卡', 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('307001'), ('622538'), ('412962'), ('412963'), ('622525'), ('622526')
) bin(BinCode)
WHERE b.StandardName = '深圳发展银行';

-- 插入银行名称别名数据
-- 中国工商银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('工商银行', 'COMMON'),
    ('工行', 'SHORT'),
    ('ICBC', 'ENGLISH'),
    ('工商银行股份有限公司', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '中国工商银行';

-- 中国农业银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('农业银行', 'COMMON'),
    ('农行', 'SHORT'),
    ('ABC', 'ENGLISH'),
    ('农业银行股份有限公司', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '中国农业银行';

-- 中国银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('中行', 'SHORT'),
    ('BOC', 'ENGLISH'),
    ('中国银行股份有限公司', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '中国银行';

-- 中国建设银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('建设银行', 'COMMON'),
    ('建行', 'SHORT'),
    ('CCB', 'ENGLISH'),
    ('建设银行股份有限公司', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '中国建设银行';

-- 交通银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('交行', 'SHORT'),
    ('BOCOM', 'ENGLISH'),
    ('交通银行股份有限公司', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '交通银行';

-- 招商银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('招行', 'SHORT'),
    ('CMB', 'ENGLISH'),
    ('招商银行股份有限公司', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '招商银行';

-- 上海浦东发展银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('浦发银行', 'COMMON'),
    ('浦东发展银行', 'COMMON'),
    ('上海浦发银行', 'COMMON'),
    ('SPDB', 'ENGLISH')
) alias(AliasName, AliasType)
WHERE b.StandardName = '上海浦东发展银行';

-- 中国民生银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('民生银行', 'COMMON'),
    ('CMBC', 'ENGLISH'),
    ('民生银行股份有限公司', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '中国民生银行';

-- 兴业银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('CIB', 'ENGLISH'),
    ('兴业银行股份有限公司', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '兴业银行';

-- 中国光大银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('光大银行', 'COMMON'),
    ('CEB', 'ENGLISH'),
    ('光大银行股份有限公司', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '中国光大银行';

-- 中信银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('CITIC', 'ENGLISH'),
    ('中信银行股份有限公司', 'LEGACY'),
    ('中信实业银行', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '中信银行';

-- 华夏银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('HXB', 'ENGLISH'),
    ('华夏银行股份有限公司', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '华夏银行';

-- 广东发展银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('广发银行', 'COMMON'),
    ('GDB', 'ENGLISH'),
    ('广发银行股份有限公司', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '广东发展银行';

-- 平安银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('PAB', 'ENGLISH'),
    ('平安银行股份有限公司', 'LEGACY'),
    ('深圳平安银行', 'LEGACY')
) alias(AliasName, AliasType)
WHERE b.StandardName = '平安银行';

-- 深圳发展银行别名
INSERT INTO [dbo].[BankNameAlias] ([BankId], [AliasName], [AliasType], [CreatedBy])
SELECT b.Id, alias.AliasName, alias.AliasType, 'SYSTEM'
FROM [dbo].[BankInfo] b
CROSS APPLY (
    VALUES 
    ('深发银行', 'COMMON'),
    ('SDB', 'ENGLISH')
) alias(AliasName, AliasType)
WHERE b.StandardName = '深圳发展银行';

-- 查询验证数据
SELECT 
    '银行信息' as TableName,
    COUNT(*) as RecordCount
FROM [dbo].[BankInfo]
UNION ALL
SELECT 
    'BIN码映射' as TableName,
    COUNT(*) as RecordCount
FROM [dbo].[BankBinMapping]
UNION ALL
SELECT 
    '银行别名' as TableName,
    COUNT(*) as RecordCount
FROM [dbo].[BankNameAlias];

-- 显示银行信息汇总
SELECT 
    b.StandardName,
    b.ShortName,
    b.EnglishName,
    COUNT(DISTINCT bin.BinCode) as BinCount,
    COUNT(DISTINCT alias.AliasName) as AliasCount
FROM [dbo].[BankInfo] b
LEFT JOIN [dbo].[BankBinMapping] bin ON b.Id = bin.BankId AND bin.IsActive = 1
LEFT JOIN [dbo].[BankNameAlias] alias ON b.Id = alias.BankId AND alias.IsActive = 1
WHERE b.IsActive = 1
GROUP BY b.Id, b.StandardName, b.ShortName, b.EnglishName
ORDER BY b.StandardName;

PRINT '银行信息初始化完成！';
PRINT '修复说明：';
PRINT '1. 移除了广东发展银行中与深圳发展银行重复的BIN码 622525 和 622526';
PRINT '2. 深圳发展银行保留了这两个BIN码，因为历史上这两个银行确实可能共享某些BIN码';
PRINT '3. 如果需要进一步调整BIN码分配，请手动修改 BankBinMapping 表';